﻿CREATE PROCEDURE [dbo].[yaf_pmessage_list](
                @FromUserID INT  = NULL,
                @ToUserID   INT  = NULL,
                @PMessageID INT  = NULL)
AS
    BEGIN
        IF @PMessageID IS NULL
        BEGIN
            SELECT   a.*,
                     FromUser = b.Name,
                     ToUserID = c.UserID,
                     ToUser = c.Name,
                     d.IsRead,
                     d.UserPMessageID
            FROM     yaf_PMessage a,
                     yaf_User b,
                     yaf_User c,
                     yaf_UserPMessage d
            WHERE    b.UserID = a.FromUserID
            AND c.UserID = d.UserID
            AND d.PMessageID = a.PMessageID
            AND ((@ToUserID IS NOT NULL 
                  AND d.UserID = @ToUserID)
                  OR (@FromUserID IS NOT NULL 
                      AND a.FromUserID = @FromUserID))
            ORDER BY Created DESC
        END
        ELSE
        BEGIN
            SELECT   a.*,
                     FromUser = b.Name,
                     ToUserID = c.UserID,
                     ToUser = c.Name,
                     d.IsRead,
                     d.UserPMessageID
            FROM     yaf_PMessage a,
                     yaf_User b,
                     yaf_User c,
                     yaf_UserPMessage d
            WHERE    b.UserID = a.FromUserID
            AND c.UserID = d.UserID
            AND d.PMessageID = a.PMessageID
            AND a.PMessageID = @PMessageID
            AND c.UserID = @FromUserID
            ORDER BY Created DESC
        END
    END


